﻿
-- ACTIVE TABLES VIEW
CREATE VIEW [todoc].[svActiveTables]
AS
/*
LISTS ALL THE TABLES IN THE DATABASE THAT ARE ENABLED
FOR SOME FORM OF TODOC

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SELECT	SCHEMA_NAME(t.schema_id) AS TableSchema,
		t.[name] AS TableName,
		t.[schema_id],
		t.[object_id],
		MAX(CASE WHEN ep.[name] = 'Todoc: Enabled' THEN ep.value ELSE '' END) AS TodocEnabled,
		MAX(CASE WHEN ep.[name] = 'Todoc: Run' THEN ep.value ELSE '' END) AS TodocRun
FROM sys.extended_properties  ep
INNER JOIN sys.tables t
	ON ep.major_id = t.[object_id]
WHERE ep.class_desc = 'OBJECT_OR_COLUMN'
	AND ep.name IN('Todoc: Enabled', 'Todoc: Run')
GROUP BY	SCHEMA_NAME(t.schema_id),
			t.[name],
			t.[schema_id],
			t.[object_id];